package DAO;

import javabean.Customer;
import javabean.customerPackage;
import javabean.Package;

import java.sql.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class PackageDAO implements IPackageDAO {

    @Override
    public boolean addPackage(Package pkg) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean flag = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO Package (PackageName, CallTime, MobileDate, PackageDescription, ValidityPeriod, Price) VALUES (?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, pkg.getPackageName());
            ps.setInt(2, pkg.getCallTime());
            ps.setInt(3, pkg.getMobileDate());
            ps.setString(4, pkg.getPackageDescription());
            ps.setInt(5, pkg.getValidityPeriod());
            ps.setDouble(6, pkg.getPrice());

            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("套餐添加成功");
                flag = true;
            } else {
                System.out.println("套餐添加失败");
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectManager.closeConnection(conn, ps, null);
        }

        return flag;
    }


    @Override
    public Package getPackageByID(int PackageID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Package searchedPackage = null;

        try {
            conn = ConnectManager.getConnection();
            String query = "SELECT * FROM Package WHERE PackageID = ?";
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, PackageID);

            rs = pstmt.executeQuery();

            if (rs.next()) {
                int packageID = rs.getInt("PackageID");
                String packageName = rs.getString("PackageName");
                int callTime = rs.getInt("CallTime");
                int mobileDate = rs.getInt("MobileDate");
                String packageDescription = rs.getString("PackageDescription");
                int validityPeriod = rs.getInt("ValidityPeriod");
                double price = rs.getDouble("Price");

                searchedPackage = new Package(packageID, packageName, callTime, mobileDate, packageDescription, validityPeriod, price);
            }

            return searchedPackage;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }
    }


    @Override
    public boolean deletePackage(int PackageID) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        boolean result = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "DELETE FROM Package WHERE PackageID = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, PackageID);

            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                result = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            ConnectManager.closeConnection(conn, stmt, null);
        }

        return result;
    }

    @Override
    public boolean updatePackage(Package pkg) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean updated = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Package SET PackageName=?, CallTime=?, MobileDate=?, PackageDescription=?, ValidityPeriod=?, Price=? WHERE PackageID=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, pkg.getPackageName());
            ps.setInt(2, pkg.getCallTime());
            ps.setInt(3, pkg.getMobileDate());
            ps.setString(4, pkg.getPackageDescription());
            ps.setInt(5, pkg.getValidityPeriod());
            ps.setDouble(6, pkg.getPrice());
            ps.setInt(7, pkg.getPackageID());

            int rows = ps.executeUpdate();
            if (rows > 0) {
                updated = true;
                System.out.println("包裹信息更新成功。");
            } else {
                System.out.println("无法更新包裹信息。");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectManager.closeConnection(conn, ps, null);
        }

        return updated;
    }


    @Override
    public List<Package> getAllPackages() throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Package> packageList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM Package";
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                int packageID = rs.getInt("PackageID");
                String packageName = rs.getString("PackageName");
                int callTime = rs.getInt("CallTime");
                int mobileDate = rs.getInt("MobileDate");
                String packageDescription = rs.getString("PackageDescription");
                int validityPeriod = rs.getInt("ValidityPeriod");
                double price = rs.getDouble("Price");

                Package pkg = new Package(packageID, packageName, callTime, mobileDate, packageDescription, validityPeriod, price);
                packageList.add(pkg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }

        return packageList;
    }


    @Override
    public customerPackage GetCustomerPackage(Customer customer) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        customerPackage customerPackage=null;
        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM CustomerPackage WHERE CustomerID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, customer.getCustomerID());
            rs = ps.executeQuery();
            if (rs.next()) {
                int packageID = rs.getInt("PackageID");
                String phoneNumber = rs.getString("PhoneNumber");
                Date startDate = rs.getDate("StartDate");
                Date endDate = rs.getDate("EndDate");
                String packageStatus = rs.getString("PackageStatus");
                customerPackage = new customerPackage(customer.getCustomerID(), packageID, phoneNumber,
                        startDate, endDate, packageStatus);

            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            ConnectManager.closeConnection(conn, ps, rs);
        }
        return customerPackage;
    }

    @Override
    public boolean subscribePackage(Package pkg, Customer customer) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn = ConnectManager.getConnection();
            conn.setAutoCommit(false); // 关闭自动提交
            // 检查顾客是否已经订阅了该套餐
            String checkSql = "SELECT * FROM CustomerPackage WHERE CustomerID = ? AND PackageID = ?";
            pstmt = conn.prepareStatement(checkSql);
            pstmt.setString(1, customer.getCustomerID());
            pstmt.setInt(2, pkg.getPackageID());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.println("顾客已经订阅了该套餐");
                return false;
            }
            String deleteSql = "DELETE FROM CustomerPackage WHERE CustomerID = ?";
            pstmt = conn.prepareStatement(deleteSql);
            pstmt.setString(1, customer.getCustomerID());
            pstmt.executeUpdate();// 删除原有的订阅记录
            // 插入订阅记录
            String insertSql = "INSERT INTO CustomerPackage (CustomerID, PackageID, PhoneNumber, StartDate, EndDate, PackageStatus) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, customer.getCustomerID());
            System.out.println(customer.getCustomerID());
            pstmt.setInt(2, pkg.getPackageID());
            System.out.println(pkg.getPackageID());
            pstmt.setString(3, customer.getCustomerPhone());
            System.out.println(customer.getCustomerPhone());
            pstmt.setDate(4, new Date(System.currentTimeMillis()));// 设置开始日期为当前日期
            System.out.println(new Date(System.currentTimeMillis()));
            //设置结束日期为当前日期加上12个月
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new java.util.Date());
            calendar.add(Calendar.MONTH, 12);
            System.out.println(new Date(calendar.getTimeInMillis()));
            pstmt.setDate(5, new Date(calendar.getTimeInMillis()));
            pstmt.setString(6, "订阅中");
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("套餐订阅成功");
                result = true;
            } else {
                System.out.println("套餐订阅失败");
                result = false;
            }

            conn.commit(); // 提交事务
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback(); // 回滚事务
            }
            e.printStackTrace();
            result = false;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return result;
    }

    @Override
    public boolean deleteCustomerPackage(customerPackage customerpackage) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean flag = false;
        try {
            conn = ConnectManager.getConnection();
            conn.setAutoCommit(false); // 关闭自动提交
            String sql = "DELETE FROM CustomerPackage WHERE PackageID=? AND CustomerID=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, customerpackage.getPackageID());
            ps.setString(2,customerpackage.getCustomerID());
            // set parameter for the prepared statement
            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("删除套餐成功");
                flag = true;
            } else {
                System.out.println("删除套餐失败");
                flag = false;
            }
            conn.commit(); // 提交事务
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback(); // 回滚事务
            }
            e.printStackTrace();
            flag = false;
        } finally {
            ConnectManager.closeConnection(conn, ps, null);
        }
        return flag;
    }
}